package com.hqyj.javaSpringBoot.modules.test.dao;

import com.hqyj.javaSpringBoot.modules.common.entiy.SearchBean;
import com.hqyj.javaSpringBoot.modules.test.entity.City;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Repository用在持久层的接口上，这个注解是将接口的一个实现类交给spring管理
 *             这是因为该注解的作用不只是将类识别为Bean，
 *             同时它还能将所标注的类中抛出的数据访问异常封装为 Spring 的数据访问异常类型。
 * @Mapper
 */
@Repository
@Mapper
public interface CityDao {

    /**
     * 增加城市信息
     * city表有一个city_id自增长主键，如何在插入数据后自动获取到该主键值呢？
     * 可以使用@Options注解
     * 设置@Options属性userGeneratedKeys的值为true，
     * 并指定实例对象中主键的属性名keyProperty以及在数据库中的字段名keyColumn。
     * 这样在gendar插入数据后，gendarId属性会被自动赋值。
     * 当然flushCache 仍然可以设置，表示插入数据后是否更新缓存，默认是true。
     */
    @Insert("insert into test_city (city_name,local_city_name,country_id," +
            "district,population,date_modified,date_created) values " +
            "(#{cityName},#{localCityName},#{countryId},#{district},#{population}" +
            ",#{dateModified},#{dateCreated})")
    @Options(useGeneratedKeys = true,keyColumn = "city_id",keyProperty = "cityId")
    void insertCity(City city);

    /**通过城市id修改城市名称和省份*/
    @Update("update test_city set city_name=#{cityName},district=#{district} " +
            "where city_id=#{cityId}")
    void upadateCity(City city);

    /**根据城市id删除城市*/
    @Delete("delete from test_city where city_id=#{cityId}")
    void deleteCityByCityId(int cityId);

    /**根据城市id查询城市*/
    @Select("select * from test_city tc left join test_country ttc" +
            " on tc.country_id=ttc.country_id where tc.city_id=#{cityId}")
    City getCityByCityId(int cityId);

    /**根据国家id查询城市信息*/
    @Select("select * from test_city where country_id=#{countryId}")
    List<City> getCitiesByCountryId(int countryId);

    /**分页查询成绩,根据城市名称或者所属省份进行模糊查询
     * 如果script不加，则【<、>、<=、>=】符号需要转译否则不认报错，if等标签不能识别
     */
    @Select("<script>" +
                "select * from test_city " +
                "<where>" +
                    "<if test='keyWord != null and keyWord != \"\"'>" +
                        "and city_name like '%${keyWord}%' or district like '%${keyWord}%'" +
                    "</if>" +
                "</where>" +
                "<choose>" +
                    "<when test='orderBy != null and orderBy != \"\"'>" +
                    "order by #{orderBy} #{direction}" +
                    "</when>" +
                    "<otherwise>" +
                    "order by city_id" +
                    "</otherwise>" +
                "</choose>" +
            "</script>")
    List<City> getCitiesBySearchBean(SearchBean searchBean);
}
